<?php

namespace App\Models\User;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

use App\Models\Member;
use App\Models\School;
use App\Models\Sys;
use App\Models\Subscribe;
use Illuminate\Support\Facades\DB;

class User extends Model
{
    use SoftDeletes;
    protected $table = 'jxt_xp_user';

    /*public static function boot()
    {
        parent::boot();

        static::saving(function ($model) {


        });
    }*/

    //注册来源0未知 1Android 2ios 3客户端/多媒体 4web/网页端 5小程序 6第三方
    public static $reg_from_map = [
        '0' => '未知',
        '1' => 'Android',
        '2' => 'IOS',
        '3' => '客户端/多媒体',
        '4' => 'web/网页端',
        '5' => '小程序',
        '6' => '第三方',
    ];

    //性别
    public $gender_map = [
        '1' => '女',
        '2' => '男',
    ];

    //客户状态
    public static $customer_status_map = [
        '1' => '潜在客户',
        '2' => '初步接触',
        '3' => '持续跟进',
        '4' => '成交客户',
        '5' => '忠诚客户',
        '6' => '无效客户',
    ];

    //重要程度
    public static $importance_map = [
        '1' => '一星',
        '2' => '二星',
        '3' => '三星',
        '4' => '四星',
        '5' => '五星',
        '6' => '六星',
    ];


    public function vip()
    {
        return $this->belongsToMany(Member\Price::class, 'jxt_member_user','user_id','vip_id')
            ->where('expiry_time', '>', time())
            ->select(['user_id','vip_id','name', 'expiry_time' , 'start_time'])
            ->orderBy('jxt_member_price.id','DESC');
    }

    public function member()
    {
        return $this->hasOne(Member\User::class,'user_id','id');
    }

    public function account()
    {
        return $this->hasOne(Account::class,'user_id','id')->select(['user_id', 'money', 'gold', 'expe']);
    }

    //创建的班级
    public function createClasses()
    {
        return $this->hasMany(School\Classes::class,'create_by')
            ->where('status','1');
        /*return $this->belongsToMany(Sys\School::class, 'jxt_school_classes','create_by','school_code')
            ->where('jxt_school_classes.status','1')
            ->select(['name','address','province','city','district','jxt_school_classes.school_name as school_name']);*/
    }

    public function classes()
    {
        return $this->belongsToMany(School\Classes::class, 'jxt_school_teacher','user_id','class_id');
    }

    //学生
    public function students()
    {
        return $this->hasMany(School\Student::class,'create_by')
            ->where('status','1');
    }

    //订阅班级
    public function subClass()
    {
        return $this->hasMany(Subscribe\Info::class,'user_id')
            ->where('status','1');
    }

    //孩子
    public function child()
    {
        return $this->hasMany(Sys\Child::class,'create_by')
            ->where('status','1');
    }

    public function address()
    {
        return $this->hasMany(Sys\Address::class, 'user_id');
    }

    /*public function getAvatarAttribute()
    {
        return 'http://test.keji110.com' . $this->attributes['avatar'];
    }*/

    public function getRegFromAttribute()
    {
        return static::$reg_from_map[$this->attributes['reg_from']] ?? '';
    }

    public function getBirthdayAttribute()
    {
        return $this->attributes['birthday'] ? date('Y-m-d H:i:s',$this->attributes['birthday']) : '--';
    }

    /*public function getGenderAttribute()
    {
        return $this->gender_map[$this->attributes['gender']] ?? '保密';
    }*/

    /*public function getCustomerStatusAttribute()
    {
        return $this->customer_status_map[$this->attributes['customer_status']] ?? '--';
    }

    public function getImportanceAttribute()
    {
        return $this->importance_map[$this->attributes['importance']] ?? '--';
    }*/


    public function getTeacher()
    {
        /*return $this->leftJoin('jxt_user_nexus as a',$this->table.'.id','a.user_id')
            ->where([
                ['a.data_type', '1']
            ])
            ->select([
                $this->table.'.id',
                'username',
                'nickname',
                'telephone',
                'created_at',
                'role_id',
                'initial_role_id',
                'customer_status',
                'importance',
                'note',
            ]);*/

        return $this->leftJoin('jxt_user_nexus as a', function ($join){
            $join->on('jxt_xp_user.id','a.user_id')->where('a.data_type','=','1');
        });

    }

    public function exchange()
    {
        return $this->hasMany(Sys\Exchange::class,'user_id','id');
    }

    public function statisticsRegisterByMonth($role_type = 0, $month)
    {
        $row = $this::when($role_type,function ($query) use ($role_type) {
                    return $query->where('initial_role_id',$role_type);
                })
            ->select([DB::raw('DATE_FORMAT(created_at, "%Y-%m-%d") as date'),DB::raw('COUNT(*) as count')])
            ->where(DB::raw('DATE_FORMAT(created_at, "%Y-%m")'), $month)
            ->groupBy(DB::raw('DATE_FORMAT(created_at, "%Y-%m-%d")'))
            ->get()->toArray();

        $data['date']  = array_column($row,'date');
        $data['count'] = array_column($row,'count');

        return $data;
    }

    public function statisticsRegisterByYear($role_type = 0, $year)
    {
        $row = $this::when($role_type,function ($query) use ($role_type) {
                return $query->where('initial_role_id',$role_type);
            })
            ->select([DB::raw('DATE_FORMAT(created_at, "%Y-%m") as date'),DB::raw('COUNT(*) as count')])
            ->where(DB::raw('DATE_FORMAT(created_at, "%Y")'), $year)
            ->groupBy(DB::raw('DATE_FORMAT(created_at, "%Y-%m")'))
            ->get()->toArray();

        $data['date']  = array_column($row,'date');
        $data['count'] = array_column($row,'count');

        return $data;
    }

    public static function school($user_id, $role_id)
    {
        if ($role_id == 1) {
            return School\Teacher::leftJoin('jxt_school_classes as b', 'b.class_id', 'jxt_school_teacher.class_id')
                ->leftJoin('jxt_sys_school as c', 'c.id', 'b.school_code')
                ->where('jxt_school_teacher.user_id', $user_id)
                ->where('jxt_school_teacher.status', '1')
                ->where('b.status', '1')
                ->selectRaw('jxt_school_teacher.user_id,jxt_school_teacher.class_name,b.name,b.school_name as s_name,b.school_code,c.school_name,c.province,c.city,c.district,c.address')
                ->get();
        } else {
            return Sys\Child::leftJoin('jxt_school_child_student as b', 'b.child_id', 'jxt_sys_child.child_id')
                ->leftJoin('jxt_school_student as c', 'c.student_id', 'b.student_id')
                ->leftJoin('jxt_school_classes as d', 'd.class_id', 'c.class_id')
                ->leftJoin('jxt_sys_school as e', 'e.id', 'd.school_code')
                ->where('jxt_sys_child.create_by', $user_id)
                ->where('jxt_sys_child.status', '1')
                ->where('b.status', '1')
                ->where('c.status', '1')
                ->where('d.status', '1')
                ->selectRaw('d.name,d.school_name AS s_name,d.school_code,e.school_name,e.province,e.city,e.district,e.address')
                ->get();
        }

    }


}
